Release 10.1A: OpenEdge Development:
Programming Interfaces

Specifying joins in the 4GL

Progress supports two types of joins in the 4GL:

Inner join — Supported in all statements capable of reading multiple tables, including the FOR, DO, REPEAT, and OPEN QUERY statements. An inner join returns the records selected for the table (or join) on the left side combined with the related records selected from the table on the right. For any records not selected from the right-hand table, the join returns no records from either the left or right sides of the join. Thus, only related records that are selected from both sides are returned for an inner join. Table 1–3 shows an example of inner joins.
Left outer join — Supported only in the OPEN QUERY statement. A left outer join returns the records selected for an inner join. In addition, for each set of records selected from the table (or join) on the left side, a left outer join returns the Unknown value (?) from the table on the right where there is no record selected or otherwise related to the records on the left. That is, records from the left-hand table (or join) are preserved for all unmatched records in the right-hand table. Figure 1–4 shows an example of left outer joins using the same tables as in Figure 1–3.
Note: For Progress versions earlier than V8, Progress supports only the inner join.

Specifying the type of join

The Record phrase that specifies the right-hand table of a join also indicates the type of join operation. A Record phrase specifies an inner join by default. To specify a left outer join, you include the [LEFT] OUTER–JOIN option anywhere in the Record phrase. Where you specify a list of multiple Record phrases in a record-reading statement, the join logic allows you to specify only one set of contiguous inner joins at the beginning (left side) of the list and one set of contiguous left outer joins at the end (right side) of the list. Each right-hand Record phrase of a left outer join must contain the OUTER–JOIN option up to and including the last left outer join in the list. For more information, see the "Mixing inner and left outer joins" section.

Relating and selecting tables

You can specify join conditions (table relations) using the OF option or WHERE option of the Record phrase that specifies the join. The OF option specifies an implicit join condition based on one or more common field names in the specified tables. The common field names must participate in a unique index for at least one of the tables. The WHERE option can specify an explicit join based on any field relations you choose, and you can use this option further to specify selection criteria for each table in the join. For an inner join, if you do not use either option, Progress returns a join of all records in the specified tables. For a left outer join, you must relate tables and select records using the OF option, the WHERE option, or both options.

Note: Work tables and temporary tables can also participate in joins. However, work tables do not have indexes. So, if you specify join conditions using the OF option with a work table, the other table in the join must be a database or temporary table with a unique index for the fields in common. For more information on work tables and temporary tables, see OpenEdge Development: Progress 4GL Handbook .

The following code fragment generates the left outer joins shown in Figure 1–4. Note that the Record phrase for the right-hand table of each join specifies the OUTER–JOIN option. As Figure 1–4 shows, the primary benefit of a left outer join is that it returns every record on the left-hand side, whether or not related data exists on the right:

DEFINE QUERY q1 FOR Table1, Table2, Table3. OPEN QUERY q1 FOR EACH Table1, EACH Table2 OUTER-JOIN WHERE C11 = C21,     EACH Table3 OUTER-JOIN WHERE C22 = C31. GET FIRST q1. DO WHILE AVAILABLE(Table1):     DISPLAY C11 C12 C21 C22 C31 C32 WITH TITLE "Join123".     GET NEXT q1. END.

Figure 1–4: Left outer joins

Why use joins instead of nested reads?

Using joins provides an opportunity for Progress to optimize the retrieval of records from multiple related tables using the selection criteria you specify. When you perform a nested read, for example using nested FOR EACH statements for different tables, you are actually implementing a join in a 4GL procedure. However, by specifying one or more contiguous joins in a single FOR EACH statement or in the PRESELECT phrase of single DO or REPEAT statement, you minimize the complexity of your 4GL code and leave the complexity of joining tables to the Progress interpreter.

For a single 4GL query (OPEN QUERY statement), there is no other way to retrieve data from multiple tables except by using joins. With both inner and left outer join capability, you can use the OPEN QUERY statement to implement most queries that are possible using nested FOR EACH, DO, or REPEAT statements. As such, query joins provide the greatest opportunity for optimized multi-table record retrieval in the 4GL. Also, because browse widgets read their data from queries, you must use query joins to display multiple related tables in a browse. (For more information on browsing records, see OpenEdge Development: Progress 4GL Handbook .)

However, use nested FOR EACH, DO, and REPEAT blocks wherever you require much finer control over how you access and manipulate records from multiple tables.